# coding:utf8
from scrapydemo.dataSourcu_mysql.MysqlUtils import MysqlUtils
import datetime
import uuid
import os

# 获取数据库连接
mysql=MysqlUtils()


"""
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sourcce` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '来源或者url',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系人姓名',
  `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
  `tel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系电话',
  `business_scope` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '服务范围或主营产品',
  `compony_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '公司名称',
  `compony_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '公司地址',
  `postcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮编',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
"""


# 插入数据
def insertUser(id=None,sourcce=None,name=None,phone=None,tel= None,business_scope=None
               ,compony_name=None,compony_address=None,postcode=None,email=None,time=None):
    if id is None:
        id= uuid.uuid1()
        # print(type(str(id)))
    sql= []
    baseSql = " INSERT INTO `tb_user3`(`id`, `sourcce`, `name`, `phone`, `tel`, `business_scope`, `compony_name`, `compony_address`, `postcode`, `email`, `time`) VALUES ( "
    sql.append(baseSql)
    sql.append("'")
    sql.append(str(id))
    sql.append("','")
    if sourcce is None:
        sql.append('')
    else:
        sql.append(''.join(sourcce))
    sql.append("','")
    if name is None:
        sql.append('')
    else:
        sql.append(''.join(name))
    sql.append("','")
    if phone is None:
        sql.append('')
    else:
        sql.append(phone)
    sql.append("','")
    if tel is None:
        sql.append('')
    else:
        sql.append(tel)
    sql.append("','")
    if business_scope is None:
        sql.append('')
    else:
        sql.append(''.join(business_scope))
    sql.append("','")
    if compony_name is None:
        sql.append('')
    else:
        sql.append(''.join(compony_name))
    sql.append("','")
    if compony_address is None:
        sql.append('')
    else:
        sql.append(''.join(compony_address))
    sql.append("','")
    if postcode is None:
        sql.append('')
    else:
        sql.append(postcode)
    sql.append("','")
    if email is None :
        sql.append('')
    else:
        sql.append(email)
    sql.append("','")
    if time is None:
        sql.append('')
    else:
        sql.append(time)
    sql.append("' )")
    strSql=''.join(sql)
    row= mysql.insertOrUdate(strSql)
    if(row<1):
        print('新增失败')

# 修改
def updateUser(id,dict):
    keys=dict.keys()
    sql =''
    for key in keys:
        sql+='`'+key+'` = '+"'"+dict[key]+"',"

    updateSql = "UPDATE `tb_user3` SET "+sql[:-1]+" WHERE `id` = '"+id+"' ";
    row= mysql.insertOrUdate(updateSql)
    if(row<1):
        print('修改失败')

